﻿CREATE PROCEDURE [maint].[SetDefaultBackupDir]
@NewBackupDir VARCHAR (256)=NULL
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Server int ,@ActionDesc varchar(255) ,@hr int;

	SET @ActionDesc = 'instantiate SQL-DMO Server'
	EXEC @hr = sp_OACreate 'SQLDMO.SQLServer2', @Server OUT
	IF (@hr <> 0) GOTO ERROR_HANDLER

	DECLARE @RefObject int
	SET @RefObject = @Server

	SET @ActionDesc = 'set login to windows authentication on ' + @@SERVERNAME
	EXEC @hr = sp_OASetProperty @Server, 'LoginSecure', 1
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @ActionDesc = 'use windows authentication to log-on to ' + @@SERVERNAME
	EXEC @hr = sp_OAMethod @Server, 'Connect', NULL, @@SERVERNAME
	IF (@hr <> 0) GOTO ERROR_HANDLER

	DECLARE @Registry int
	SET @ActionDesc = 'access the registry'
	EXEC @hr = sp_OAGetProperty @Server, 'Registry', @Registry output
	IF (@hr <> 0) GOTO ERROR_HANDLER

	DECLARE @DefaultBackupDir varchar(255)
	SET @RefObject = @Registry
	SET @ActionDesc = 'get the default Backup directory'
	EXEC @hr=sp_OAGetProperty @Registry, 'Backupdirectory', @DefaultBackupDir output
	IF (@hr <> 0) GOTO ERROR_HANDLER

	IF (@NewBackupDir IS NOT NULL)
		BEGIN
			SET @ActionDesc = 'change the backup directory'
			EXEC @hr = sp_OASetProperty @Registry, 'Backupdirectory', @NewBackupDir
			IF (@hr <> 0) GOTO ERROR_HANDLER
		END

	SET @RefObject = @Server
	SET @ActionDesc = 'disconnect from the server'
	EXEC @hr = sp_OAMethod @Server, 'DisConnect'
	IF (@hr <> 0) GOTO ERROR_HANDLER

	GOTO EXIT_PROC

ERROR_HANDLER:
	DECLARE @ErrorSrc varchar(255) ,@ErrorDesc varchar(255) ,@Helpfile varchar(255) ,@HelpID int
	EXECUTE sp_OAGetErrorInfo @RefObject, @ErrorSrc OUTPUT ,@ErrorDesc OUTPUT ,@Helpfile OUTPUT ,@HelpID OUTPUT

	DECLARE @ErrorMsg varchar(512)
	SET @ErrorMsg = 'Error Source: ' + ISNULL(@ErrorSrc,'')
		+ ' , Error Description: ' + ISNULL(@ErrorDesc,'')
		+ ' , Help File: ' + ISNULL(@Helpfile,'')
		+ ' , Help ID: ' + ISNULL(CAST(@HelpID AS varchar),'')
		+ CHAR(10)
		+ 'Failed to ' + ISNULL(@ActionDesc,'execute')
	RAISERROR (N'Registry update failed. Check if the SQL Server Service account has access to the registry.',16,1)
	RAISERROR (@ErrorMsg,16,1)

EXIT_PROC:
	EXEC sp_OADestroy @Server

	IF (@hr = 0)
		PRINT 'Default backup directory: ' + ISNULL(@NewBackupDir,@DefaultBackupDir)

	RETURN(@hr)
END